#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pandas as pd
import sys
import os

def extract_apn_data_with_carriers(file_path, sheet_name='FS045W'):
    """从Excel文件中提取APN数据，包含运营商信息"""
    print(f"正在提取 {sheet_name} 工作表的数据...")
    
    # 读取Excel文件，不设置header，以便分析运营商分组
    df_raw = pd.read_excel(file_path, sheet_name=sheet_name, header=None)
    
    # 分析运营商分组
    carriers = analyze_carrier_sections(df_raw)

    # 存储所有APN数据
    all_apn_data = []
    # 存储所有运营商数据
    all_carrier_data = []
    
    for carrier in carriers:
        print(f"\n处理运营商: {carrier['name']} (PLMN: {carrier['plmn']})")

        for one in carrier['plmn'].split():
            one_carrier = {}
            one_carrier['carrier'] = carrier['name']
            one_carrier['mcc'] = one[0:3]
            one_carrier['mnc'] = one[3:5]
            all_carrier_data.append(one_carrier)
        
        # 找到该分组的表头行
        header_row = None
        for row_idx in range(carrier['start_row'], min(carrier['start_row'] + 5, carrier['end_row'] + 1)):
            if row_idx < len(df_raw):
                row_data = df_raw.iloc[row_idx].dropna().tolist()
                if any('Search Priority' in str(cell) for cell in row_data):
                    header_row = row_idx
                    break
        
        if header_row is None:
            print(f"  警告: 未找到表头行，跳过运营商 {carrier['name']}")
            continue
        
        # 提取该运营商的数据
        data_start = header_row + 1
        data_end = carrier['end_row'] + 1
        
        # 确保数据范围有效
        if data_start >= len(df_raw) or data_end <= data_start:
            print(f"  警告: 数据范围无效，跳过运营商 {carrier['name']}")
            continue
        
        # 使用原始数据框直接提取该分组的数据
        carrier_rows = []
        for row_idx in range(data_start, min(data_end, len(df_raw))):
            row_data = df_raw.iloc[row_idx].tolist()
            # 过滤有效数据行（至少有3个非空值）
            non_null_count = sum(1 for x in row_data if pd.notna(x) and str(x).strip())
            if non_null_count >= 3:
                carrier_rows.append(row_data)
        
        if not carrier_rows:
            print(f"  警告: 未找到有效数据行，跳过运营商 {carrier['name']}")
            continue
        
        # 创建DataFrame
        columns = ['col_0', 'Search Priority', 'profile name', 'ID', 'PW', 'APN', 'Auth', 'IPType', 'col_8']
        carrier_df = pd.DataFrame(carrier_rows, columns=columns[:len(carrier_rows[0])])
        
        # 清理数据
        carrier_df = carrier_df.dropna(how='all')
        
        # 删除Search Priority列为空的行
        if 'Search Priority' in carrier_df.columns:
            carrier_df = carrier_df.dropna(subset=['Search Priority'])
        
        # 添加运营商信息
        carrier_df['carrier'] = carrier['name']
        carrier_df['plmn'] = carrier['plmn']
        
        print(f"  提取到 {len(carrier_df)} 条APN记录")
        all_apn_data.append(carrier_df)
    
    # 合并所有数据
    if all_apn_data:
        final_df = pd.concat(all_apn_data, ignore_index=True)
        print(f"\n总共提取到 {len(final_df)} 条APN记录")
        return (final_df, all_carrier_data)
    else:
        print("\n未提取到任何APN记录")
        return (pd.DataFrame(), all_carrier_data)

def analyze_carrier_sections(df_raw):
    """分析运营商分组"""
    import re
    
    carriers = []
    
    # 遍历每一行查找运营商信息
    for index, row in df_raw.iterrows():
        # 检查是否包含PLMN信息（运营商标识行）
        row_str = ' '.join([str(cell) for cell in row if pd.notna(cell)])
        
        if 'PLMN' in row_str:
            # 提取运营商名称和PLMN代码
            #plmn_match = re.search(r'PLMN\s*(\d+)', row_str)
            #plmn_code = plmn_match.group(1) if plmn_match else "未知PLMN"
            plmn_match = re.search(r'PLMN\s*([\d/]+)', row_str)
            plmn_code = re.findall(r"\d+", plmn_match.group(1)) if plmn_match else "未知PLMN"
            plmn_code = ' '.join(plmn_code)
            
            # 提取运营商名称（PLMN前面的文本）
            carrier_match = re.search(r'(\w+).*?PLMN', row_str)
            carrier_name = carrier_match.group(1) if carrier_match else "未知运营商"
            
            carriers.append({
                'name': carrier_name,
                'plmn': plmn_code,
                'start_row': index,
                'full_text': row_str.strip()
            })
    
    # 计算每个运营商的数据范围
    for i, carrier in enumerate(carriers):
        if i < len(carriers) - 1:
            carrier['end_row'] = carriers[i + 1]['start_row'] - 1
        else:
            carrier['end_row'] = len(df_raw) - 1
    
    return carriers

def convert_to_uci_format(df, carriers):
    uci_content = []
    uci_content.append("# APN LIST Configuration")
    uci_content.append("# Generated from APNList.xlsx with Carrier Support")
    current_time = pd.Timestamp.now().strftime("%Y-%m-%d %H:%M:%S")
    uci_content.append(f"# generated_time: {current_time}")
    uci_content.append("")

    # 生成运营商配置
    uci_content.append("config 'feature' 'carrier_info'")
    uci_content.append(f"\toption 'count' '{len(carriers)}'")
    uci_content.append("")
    for one in carriers:
        uci_content.append(f"config 'carrier_info'")
        uci_content.append(f"\toption carrier '{one['carrier']}'")
        uci_content.append(f"\toption mcc '{one['mcc']}'")
        uci_content.append(f"\toption mnc '{one['mnc']}'")
        uci_content.append("")
    
    config_counter = 0
    valid_configs = []
    
    # 首先处理所有数据，收集有效配置
    for index, row in df.iterrows():
        try:
            # 获取基本信息
            priority = int(row['Search Priority']) if pd.notna(row['Search Priority']) else index + 1
            profile_name = str(row['profile name']).strip() if pd.notna(row['profile name']) else f"apn_{config_counter + 1}"
            apn = str(row['APN']).strip() if pd.notna(row['APN']) else ""
            username = str(row['ID']).strip() if pd.notna(row['ID']) else ""
            password = str(row['PW']).strip() if pd.notna(row['PW']) else ""
            auth_type = str(row['Auth']).strip().lower() if pd.notna(row['Auth']) else "none"
            ip_type = str(row['IPType']).strip().lower() if pd.notna(row['IPType']) else "ipv4"
            
            # 获取运营商信息
            carrier = str(row['carrier']).strip() if pd.notna(row['carrier']) else "unknown"
            plmn = None #str(row['plmn']).strip() if pd.notna(row['plmn']) and 'plmn' in row else ""
            
            # 清理数据
            if username.lower() == 'nan':
                username = ""
            if password.lower() == 'nan':
                password = ""
            
            # 转换认证类型
            if auth_type == 'chap':
                auth_method = '2'
            elif auth_type == 'pap':
                auth_method = '1'
            else:
                auth_method = '0'
            
            # 转换IP类型
            if 'ipv6' in ip_type:
                pdp_type = '1'
            elif 'ipv4v6' in ip_type or 'dual' in ip_type:
                pdp_type = '2'
            else:
                pdp_type = '0'
            
            config_counter += 1
            
            # 存储有效配置信息
            valid_configs.append({
                'profile_name': profile_name,
                'apn': apn,
                'carrier': carrier,
                'plmn': plmn,
                'username': username,
                'password': password,
                'auth_method': auth_method,
                'pdp_type': pdp_type
            })
            
            print(f"已处理: {profile_name} ({carrier}) -> {apn}")
            
        except Exception as e:
            print(f"处理第 {index + 1} 行时出错: {e}")
            continue
    
    # 添加auto_apn配置块（记录匿名session数量）
    uci_content.append("config 'feature' 'auto_apn'")
    uci_content.append(f"\toption 'count' '{len(valid_configs)}'")
    #current_time = pd.Timestamp.now().strftime("%Y-%m-%d %H:%M:%S")
    #uci_content.append(f"\toption 'generated_time' '{current_time}'")
    uci_content.append("")
    
    # 生成匿名APN配置块
    for i, config in enumerate(valid_configs):
        # 使用匿名session格式（不指定名称）
        uci_content.append(f"config 'auto_apn'")
        uci_content.append(f"\toption 'carrier' '{config['carrier']}'")
        uci_content.append(f"\toption 'profile_name' '{config['profile_name']}'")
        uci_content.append(f"\toption 'name' '{config['apn']}'")
        if config['plmn']:
            uci_content.append(f"\toption 'plmn' '{config['plmn']}'")
        if config['username']:
            uci_content.append(f"\toption 'user' '{config['username']}'")
        else:
            uci_content.append(f"\toption 'user' ''")
        if config['password']:
            uci_content.append(f"\toption 'passwd' '{config['password']}'")
        else:
            uci_content.append(f"\toption 'passwd' ''")
        uci_content.append(f"\toption 'auth' '{config['auth_method']}'")
        uci_content.append(f"\toption 'ipmode' '{config['pdp_type']}'")
        uci_content.append(f"\toption 'profile_prop' '1'")
        uci_content.append("")
    
    return "\n".join(uci_content)

def main():
    file_path = "test.xlsx"
    
    if not os.path.exists(file_path):
        print(f"错误: 找不到文件 {file_path}")
        return
    
    try:
        # 提取FS045W工作表的数据（包含运营商信息）
        df_current, carriers = extract_apn_data_with_carriers(file_path, 'FS045W')
        print(f"已找到 {carriers}运营商信息")

        # 转换为UCI格式
        uci_content = convert_to_uci_format(df_current, carriers)
        
        # 保存到文件
        output_file = "apn_config.uci"
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write(uci_content)
        
        print(f"\n✅ UCI配置已保存到: {output_file}")
        print(f"文件大小: {len(uci_content)} 字符")
        
        '''
        # 显示前几行内容
        print("\n配置文件预览:")
        print("-" * 50)
        lines = uci_content.split('\n')
        for line in lines[:20]:  # 显示前20行
            print(line)
        if len(lines) > 20:
            print("...")
            print(f"... (还有 {len(lines) - 20} 行)")
        '''
    except Exception as e:
        print(f"错误: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    main()